<?php


##########################################################################
############### Library of Data Manipulation Functions ###################
##########################################################################



##########################################################################
############### Data Interpolation function            ###################
##########################################################################


   function genericInterp($listobject, $thisyear, $srctable, $desttable, $timecol, $keycols, $interpcol, $defaultmethod, $defaultvalue, $temptable, $debug, $extrawhere='') {
      # $srctable - the table containing the values to draw from for this interpolation
      # $defaultmethod - how to handle cases where there is either 1 or zero bounding data points
      # This method expects there to be 2 points, one in the future, and one in the past in order to
      # perform an interpolation. If not, some method must be specified to arrive at a value for
      # this point.
      #       0 - Always use the $defaultvalue
      #       1 - Use only a prior point, otherwise, use the $defaultvalue
      #       2 - Use only a future point, otherwise, use the $defaultvalue
      #       3 - Use either a future point or past point rather than the $defaultvalue
      #            Check first for prior, then for future point

      # $temptable - whether or not to use the descriptor 'TEMP' in front of table creation syntax
      #              this serves two purposes
      #              0) it supports the temp table syntax used in MS SQL Server where the 'TEMP' keyword does not exist
      #              1) it allows you to make either temporary or permanent tables with this
      if ($temptable) {
         $tstring = 'TEMP';
      } else {
         $tstring = '';
      }

      if (ltrim(rtrim($extrawhere)) == '') {
         $extrawhere = '( 1 = 1) ';
      }

      # $keyvcols - comma separated list of columns that must match to interpolate data on
      $keyar = explode(",", $keycols);

      $nexttab = ltrim(rtrim($desttable)) . "_next";
      $prevtab = ltrim(rtrim($desttable)) . "_prev";
      $dpdy = ltrim(rtrim($desttable)) . "_dpdy";
      $interpcol = ltrim(rtrim($interpcol));

      $listobject->querystring = " SELECT $keycols, cast(-88888 AS float) as $interpcol,  ";
      $listobject->querystring .= " min($timecol) AS nextyear ";
      $listobject->querystring .= " INTO $tstring $nexttab ";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $timecol > $thisyear  ";
      $listobject->querystring .= "    AND $extrawhere  ";
      $listobject->querystring .= " GROUP BY $keycols ";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      $listobject->querystring = " SELECT $keycols, cast(-88888 AS float) as $interpcol,  ";
      $listobject->querystring .= " max($timecol) AS prevyear ";
      $listobject->querystring .= " INTO $tstring $prevtab ";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $timecol < $thisyear  ";
      $listobject->querystring .= "    AND $extrawhere  ";
      $listobject->querystring .= " GROUP BY $keycols ";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      $listobject->querystring = " SELECT $keycols, cast(-88888 AS float) as $interpcol, ";
      $listobject->querystring .= " $thisyear as $timecol";
      $listobject->querystring .= " INTO $tstring $desttable ";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= "    AND $extrawhere  ";
      $listobject->querystring .= " GROUP BY $keycols";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # set values for previous reported year
      $listobject->querystring = " UPDATE $nexttab ";
      $listobject->querystring .= " SET $interpcol = $srctable.$interpcol";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= "    AND $extrawhere  ";
      $listobject->querystring .= " WHERE ($srctable.$timecol=$nexttab.nextyear) ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($srctable.$thiskey=$nexttab.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # set values for next reported year
      $listobject->querystring = " UPDATE $prevtab ";
      $listobject->querystring .= " SET $interpcol = $srctable.$interpcol";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE ($srctable.$timecol = $prevtab.prevyear)  ";
      $listobject->querystring .= "    AND $extrawhere  ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($srctable.$thiskey=$prevtab.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # calculate change in animals per year between previous and next
      $listobject->querystring = " SELECT a.prevyear ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= ", a.$thiskey ";
      }
      $listobject->querystring .= " , $thisyear as $timecol,  a.$interpcol as prevdata, ";
      $listobject->querystring .= " b.nextyear, b.$interpcol as nextdata, ";
      $listobject->querystring .= " (cast((b.$interpcol-a.$interpcol) as float)) / (cast((b.nextyear-a.prevyear) as float8)) AS dpdy,";
      $listobject->querystring .= " cast(-88888 AS float) AS $interpcol ";
      $listobject->querystring .= " INTO $tstring $dpdy ";
      $listobject->querystring .= " FROM $prevtab AS a, $nexttab AS b";
      $listobject->querystring .= " WHERE a.$interpcol <> -88888";
      $listobject->querystring .= "    and b.$interpcol <> -88888";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND (a.$thiskey=b.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # calcuale estimated interpolated population for each year in the table
      $listobject->querystring = " UPDATE $dpdy SET $interpcol = ";
      $listobject->querystring .= " (prevdata+(($timecol - prevyear)* dpdy))";
      $listobject->querystring .= " where prevdata <> -88888";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # if the year is an exact match, grab the corresponding entry
      $listobject->querystring = " UPDATE $desttable SET $interpcol = $srctable.$interpcol";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $desttable.$timecol = $srctable.$timecol ";
      $listobject->querystring .= "    AND $extrawhere  ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($desttable.$thiskey=$srctable.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # otherwise, grab from the interpolation table
      $listobject->querystring = " UPDATE $desttable SET $interpcol = $dpdy.$interpcol ";
      $listobject->querystring .= " FROM $dpdy ";
      $listobject->querystring .= " WHERE $desttable.$interpcol = -88888 ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($desttable.$thiskey=$dpdy.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # if still not set, must be missing either the previous or prior value
      # consult the choice of defaultmethod:
      if ( ($defaultmethod == 1) or ($defaultmethod == 3) ) {
         # look for a previous value
         $listobject->querystring = " UPDATE $desttable set $interpcol = $prevtab.$interpcol ";
         $listobject->querystring .= " FROM $prevtab ";
         $listobject->querystring .= " WHERE $desttable.$interpcol = -88888 ";
         $listobject->querystring .= " AND $prevtab.$interpcol <> -88888";
         reset($keyar);
         foreach ($keyar as $thiskey) {
            $thiskey = ltrim(rtrim($thiskey));
            $listobject->querystring .= "    AND ($desttable.$thiskey=$prevtab.$thiskey)  ";
         }
         $listobject->performQuery();
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }
      }

      if (($defaultmethod == 2) or ($defaultmethod == 3)) {
         # look for a future value
         $listobject->querystring = " UPDATE $desttable set $interpcol = $nexttab.$interpcol ";
         $listobject->querystring .= " FROM $nexttab ";
         $listobject->querystring .= " WHERE $desttable.$interpcol = -88888 ";
         $listobject->querystring .= " AND $nexttab.$interpcol <> -88888";
         reset($keyar);
         foreach ($keyar as $thiskey) {
            $thiskey = ltrim(rtrim($thiskey));
            $listobject->querystring .= "    AND ($desttable.$thiskey=$nexttab.$thiskey)  ";
         }
         $listobject->performQuery();
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }
      }

      # Finally, use the default value (this is the last resort in all cases)
      $listobject->querystring = " UPDATE $desttable set $interpcol = $defaultvalue ";
      $listobject->querystring .= " WHERE $desttable.$interpcol = -88888 ";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

   }


   function genericMultiInterp($listobject, $thisyear, $srctable, $desttable, $timecol, $keycols, $interpcols, $defaultmethod, $defaultvalue, $temptable, $nullvalue, $debug, $extrawhere='') {
      # $srctable - the table containing the values to draw from for this interpolation
      # $defaultmethod - how to handle cases where there is either 1 or zero bounding data points
      # This method expects there to be 2 points, one in the future, and one in the past in order to
      # perform an interpolation. If not, some method must be specified to arrive at a value for
      # this point.
      #       0 - Always use the $defaultvalue
      #       1 - Use only a prior point, otherwise, use the $defaultvalue
      #       2 - Use only a future point, otherwise, use the $defaultvalue
      #       3 - Use either a future point or past point rather than the $defaultvalue
      #            Check first for prior, then for future point

      # $temptable - whether or not to use the descriptor 'TEMP' in front of table creation syntax
      #              this serves two purposes
      #              0) it supports the temp table syntax used in MS SQL Server where the 'TEMP' keyword does not exist
      #              1) it allows you to make either temporary or permanent tables with this
      # $nullvalue - the value to set columns to initially to indicate that they have not been updated (eg -99999)
      if ($temptable) {
         $tstring = 'TEMP';
      } else {
         $tstring = '';
      }

      if ($debug) {
         print("genericMultiInterp routine called. <BR> ");
      }

      if (ltrim(rtrim($extrawhere)) == '') {
         $extrawhere = '( 1 = 1) ';
      }
      # $keyvcols - comma separated list of columns that must match to interpolate data on
      $keyar = explode(",", $keycols);

      $nexttab = ltrim(rtrim($desttable)) . "_next";
      $prevtab = ltrim(rtrim($desttable)) . "_prev";
      $dpdy = ltrim(rtrim($desttable)) . "_dpdy";
      $interpar = explode(',', $interpcols);
      $firstcol = ltrim(rtrim($interpar[0]));

      if (!$temptable) {
         $listobject->querystring = " drop table $nexttab ";
         $listobject->performQuery();
         $listobject->querystring = " drop table $prevtab ";
         $listobject->performQuery();
         $listobject->querystring = " drop table $dpdy ";
         $listobject->performQuery();
      }

      $listobject->querystring = " SELECT $keycols, ";
      reset($interpar);
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= " cast($nullvalue AS float) as $interpcol,  ";
      }
      $listobject->querystring .= " min($timecol) AS nextyear ";
      $listobject->querystring .= " INTO $tstring $nexttab ";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $timecol > $thisyear  ";
      $listobject->querystring .= "    AND $extrawhere  ";
      $listobject->querystring .= " GROUP BY $keycols ";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      $listobject->querystring = " SELECT $keycols, ";
      reset($interpar);
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= " cast($nullvalue AS float) as $interpcol,  ";
      }
      $listobject->querystring .= " max($timecol) AS prevyear ";
      $listobject->querystring .= " INTO $tstring $prevtab ";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $timecol < $thisyear  ";
      $listobject->querystring .= "    AND $extrawhere  ";
      $listobject->querystring .= " GROUP BY $keycols ";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      $listobject->querystring = " SELECT $keycols, ";
      reset($interpar);
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= " cast($nullvalue AS float) as $interpcol,  ";
      }
      $listobject->querystring .= "  $thisyear as $timecol";
      $listobject->querystring .= " INTO $tstring $desttable ";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $extrawhere  ";
      $listobject->querystring .= " GROUP BY $keycols";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # set values for previous reported year
      $listobject->querystring = " UPDATE $nexttab SET ";
      reset($interpar);
      $idel = '';
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= " $idel $interpcol = $srctable.$interpcol  ";
         $idel = ',';
      }
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE ($srctable.$timecol=$nexttab.nextyear) ";
      $listobject->querystring .= "    AND $extrawhere  ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($srctable.$thiskey=$nexttab.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # set values for next reported year
      $listobject->querystring = " UPDATE $prevtab SET ";
      reset($interpar);
      $idel = '';
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= " $idel $interpcol = $srctable.$interpcol  ";
         $idel = ',';
      }
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE ($srctable.$timecol = $prevtab.prevyear)  ";
      $listobject->querystring .= "    AND $extrawhere  ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($srctable.$thiskey=$prevtab.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # calculate change per year between previous and next
      $listobject->querystring = " SELECT a.prevyear ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= ", a.$thiskey ";
      }
      $listobject->querystring .= " , $thisyear as $timecol, b.nextyear, ";
      reset($interpar);
      $idel = '';
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= " $idel a.$interpcol as prevdata$interpcol, ";
         $listobject->querystring .= " b.$interpcol as nextdata$interpcol, ";
         $listobject->querystring .= " (cast((b.$interpcol-a.$interpcol) as float)) ";
         $listobject->querystring .= " / (cast((b.nextyear-a.prevyear) as float8)) AS dpdy$interpcol ,  ";
         $listobject->querystring .= " cast($nullvalue AS float) AS $interpcol ";
         $idel = ',';
      }
      $listobject->querystring .= " INTO $tstring $dpdy ";
      $listobject->querystring .= " FROM $prevtab AS a, $nexttab AS b";
      $listobject->querystring .= " WHERE a.$interpcol <> $nullvalue";
      $listobject->querystring .= "    and b.$interpcol <> $nullvalue";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND (a.$thiskey=b.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # calcuale estimated interpolated population for each year in the table
      $listobject->querystring = " UPDATE $dpdy SET  ";
      reset($interpar);
      $idel = '';
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= " $idel $interpcol = ";
         $listobject->querystring .= "    (prevdata$interpcol+(($timecol - prevyear)* dpdy$interpcol)) ";
         $idel = ',';
      }
      $listobject->querystring .= " ";
      $listobject->querystring .= " where prevdata$firstcol <> $nullvalue";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # if the year is an exact match, grab the corresponding entry
      $listobject->querystring = " UPDATE $desttable SET ";
      reset($interpar);
      $idel = '';
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= "$idel $interpcol = $srctable.$interpcol ";
         $idel = ',';
      }
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $desttable.$timecol = $srctable.$timecol ";
      $listobject->querystring .= "    AND $extrawhere  ";
      # do this to take advantage of any indexes on the source table
      $listobject->querystring .= "    AND $srctable.$timecol = '$thisyear'  ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($desttable.$thiskey=$srctable.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # otherwise, grab from the interpolation table
      $listobject->querystring = " UPDATE $desttable SET  ";
      reset($interpar);
      $idel = '';
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= "$idel $interpcol = $dpdy.$interpcol ";
         $idel = ',';
      }
      $listobject->querystring .= " FROM $dpdy ";
      $listobject->querystring .= " WHERE $desttable.$interpcol = $nullvalue ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($desttable.$thiskey=$dpdy.$thiskey)  ";
      }
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

      # if still not set, must be missing either the previous or prior value
      # consult the choice of defaultmethod:
      if ( ($defaultmethod == 1) or ($defaultmethod == 3) ) {
         # look for a previous value
         $listobject->querystring = " UPDATE $desttable set ";
         reset($interpar);
         $idel = '';
         foreach ($interpar as $interpcol) {
            $interpcol = ltrim(rtrim($interpcol));
            $listobject->querystring .= "$idel $interpcol = $prevtab.$interpcol ";
            $idel = ',';
         }
         $listobject->querystring .= " FROM $prevtab ";
         $listobject->querystring .= " WHERE $desttable.$interpcol = $nullvalue ";
         $listobject->querystring .= " AND $prevtab.$interpcol <> $nullvalue";
         reset($keyar);
         foreach ($keyar as $thiskey) {
            $thiskey = ltrim(rtrim($thiskey));
            $listobject->querystring .= "    AND ($desttable.$thiskey=$prevtab.$thiskey)  ";
         }
         $listobject->performQuery();
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }
      }

      if (($defaultmethod == 2) or ($defaultmethod == 3)) {
         # look for a future value
         $listobject->querystring = " UPDATE $desttable set";
         reset($interpar);
         $idel = '';
         foreach ($interpar as $interpcol) {
            $interpcol = ltrim(rtrim($interpcol));
            $listobject->querystring .= "$idel $interpcol = $nexttab.$interpcol ";
            $idel = ',';
         }
         $listobject->querystring .= " FROM $nexttab ";
         $listobject->querystring .= " WHERE $desttable.$interpcol = $nullvalue ";
         $listobject->querystring .= " AND $nexttab.$interpcol <> $nullvalue";
         reset($keyar);
         foreach ($keyar as $thiskey) {
            $thiskey = ltrim(rtrim($thiskey));
            $listobject->querystring .= "    AND ($desttable.$thiskey=$nexttab.$thiskey)  ";
         }
         $listobject->performQuery();
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }
      }

      # Finally, use the default value (this is the last resort in all cases)
      $listobject->querystring = " UPDATE $desttable set ";
      reset($interpar);
      $idel = '';
      foreach ($interpar as $interpcol) {
         $interpcol = ltrim(rtrim($interpcol));
         $listobject->querystring .= "$idel  $interpcol = $defaultvalue ";
         $idel = ',';
      }
      $listobject->querystring .= " WHERE $desttable.$firstcol = $nullvalue ";
      $listobject->performQuery();
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }

   }

##########################################################################
############### Data Extrapolation function            ###################
############### Using Least Square Regression          ###################
##########################################################################


   function genericExtrap($listobject, $thisx, $srctable, $desttable, $xcol, $keycols, $extrapcol, $defaultvalue, $temptable, $usenull, $nullval, $rejectone, $debug, $extrapall=0, $zerox=0) {

      # this routine performs an extrapolation OR interpolation based on a least squares regression formula
      # this is based on the method used in the interpolation of ag census routines in SQL server, however,
      # it differs in that it handles both special cases where there is :
      #  1 data point - results in a constant value
      #  2 data points - results in a linear interpolation/extrapolation
      # it is up to the user to indicate that it wishes to reject 1 point cases by setting
      #   $rejectone = 1
      # $xcol = the name of the x-column of the destination table
      # $thisx = the values of the x-column to create estimates for

      # this routine WILL allow for negative values, so you must screen for these if this is not acceptable

      # $srctable - the table containing the values to draw from for this interpolation
      # $defaultmethod - how to handle cases where there is either 1 or zero bounding data points
      # This method expects there to be 2 points, one in the future, and one in the past in order to
      # perform an interpolation. If not, some method must be specified to arrive at a value for
      # this point.
      #       0 - Always use the $defaultvalue
      #       1 - Use only a prior point, otherwise, use the $defaultvalue
      #       2 - Use only a future point, otherwise, use the $defaultvalue
      #       3 - Use either a future point or past point rather than the $defaultvalue
      #            Check first for prior, then for future point

      # $temptable - whether or not to use the descriptor 'TEMP' in front of table creation syntax
      #              this serves two purposes
      #              1) it allows you to make either temporary or permanent tables with this
      #              2) it supports the temp table syntax used in MS SQL Server where the 'TEMP' keyword does not exist

      # $usenull - boolean indicates that there may be some other value that indicates a null
      # $nullval - if there is a value that indicates missing data other than NULL, check for it

      # $extrapall - whether or not to create an extrapolated value for every year in the data range, even for years in
      #   which there are already an existing value.

      # $zerox - whether or not to normalize x values on an intercept of 0.0 This helps often when the x-column in
      # a year, where we really want to do regression from 0-10 instead of 1992-2002.

      if ($zerox) {
         $listobject->querystring = "  SELECT min($xcol) as xoff ";
         $listobject->querystring .= " FROM $srctable ";
         if ($debug) {
            #print("Xoffset - $xoff <br>");
            print("$listobject->querystring ;<br>");
         }
         $listobject->performQuery();
         $xoff = $listobject->getRecordValue(1,'xoff');

      } else {
         $xoff = 0;
      }

      if ($temptable) {
         $tstring = 'TEMP';
      } else {
         $tstring = '';
      }

      # $keyvcols - comma separated list of columns that must match to interpolate data on
      $keyar = explode(",", $keycols);

      $regtab = ltrim(rtrim($desttable)) . "_regtable";
      $xxyy = ltrim(rtrim($desttable)) . "_xxyy";
      $sxxyy = ltrim(rtrim($desttable)) . "_sxxyy";
      $extrapcol = ltrim(rtrim($extrapcol));
      $xcol = ltrim(rtrim($xcol));
      $thisx = ltrim(rtrim($thisx));


      # create  base table of coefficients
      $listobject->querystring = " SELECT $keycols, count(*) AS numpoints, avg(cast(($xcol - $xoff ) as float)) AS xm, ";
      $listobject->querystring .= "    avg($extrapcol) AS ym, cast(0.0000 AS float) AS m, cast(0.0000 AS float) AS b ";
      $listobject->querystring .= " INTO $tstring $regtab ";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $extrapcol is not null ";
      if ($usenull) {
         $listobject->querystring .= "    AND $extrapcol <> $nullval ";
      }
      $listobject->querystring .= " GROUP BY $keycols";
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }
      $listobject->performQuery();


      # reject values with only one data point?
      if ($rejectone) {
         $listobject->querystring = " DELETE FROM $regtab ";
         $listobject->querystring .= " WHERE numpoints = 1 ";
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }
         $listobject->performQuery();
      }


      # calculate basic  quantities, xx, yy, and xy
      $listobject->querystring = " SELECT ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= " a.$thiskey ,";
      }
      $listobject->querystring .= " (a.$extrapcol - b.ym) AS yy, (($xcol - $xoff ) - b.xm) AS xx ";
      $listobject->querystring .= " INTO $tstring $xxyy ";
      $listobject->querystring .= " FROM $srctable AS a, $regtab AS b";
      $listobject->querystring .= " WHERE a.$extrapcol is not null ";
      if ($usenull) {
         $listobject->querystring .= "    AND a.$extrapcol <> $nullval ";
      }
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND (a.$thiskey=b.$thiskey)  ";
      }
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }
      $listobject->performQuery();


      # calculate regression quantities, Sxx, Syy, Sxy
      $listobject->querystring = " SELECT $keycols, sum(yy*yy) AS Syy, sum(xx*xx) AS Sxx, ";
      $listobject->querystring .= "    sum(xx*yy) AS Sxy ";
      $listobject->querystring .= " INTO $tstring $sxxyy ";
      $listobject->querystring .= " FROM $xxyy ";
      $listobject->querystring .= " GROUP BY $keycols ";
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }
      $listobject->performQuery();


      # calculate regression line slope and intercept for
      $listobject->querystring = " UPDATE $regtab  ";
      $listobject->querystring .= " SET m = ($sxxyy.Sxy/$sxxyy.Sxx), ";
      $listobject->querystring .= "    b = ( ym - ( xm * $sxxyy.Sxy / $sxxyy.Sxx ) ) ";
      $listobject->querystring .= " FROM $sxxyy ";
      $listobject->querystring .= " WHERE $sxxyy.Sxx <> 0 ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($sxxyy.$thiskey = $regtab.$thiskey) ";
      }
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }
      $listobject->performQuery();


      # if we choose to keep one point values, make them all eequal a constant
      #
      if (!$rejectone) {
         $listobject->querystring = " UPDATE $regtab  ";
         $listobject->querystring .= " SET m = 0.0, ";
         $listobject->querystring .= "    b = ym ";
         $listobject->querystring .= " FROM $sxxyy ";
         $listobject->querystring .= " WHERE numpoints = 1 ";
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }
         $listobject->performQuery();
      }

/*
      # show regression info
      $listobject->querystring = " select * from $regtab  ";
      $listobject->performQuery();
      $listobject->showList();
*/

      # create table of regression predicted values for the given input year
      # set up a counter to see if we have created the extrapolation table
      $k = 0;
      if ( strlen($thisx) > 0 ) {
         #$debug = 1;
         # we have called for one or more years to be specifically predicted
         $exes = explode(",", $thisx);
         foreach ($exes as $onex) {
            $listobject->querystring = '';
            if ($k > 0) {
               # only use the prefix 'temp' for the initial creation,
               # subsequent calls must only insert
               $listobject->querystring .= " INSERT INTO $desttable ($keycols, $xcol, $extrapcol) ";
            }
            $listobject->querystring .= " SELECT $keycols, $onex as $xcol,  ";
            $listobject->querystring .= " ( m * cast(( $onex - $xoff ) as float) + b ) AS $extrapcol ";
            if ($k == 0) {
               # only select-into for the initial creation,
               # subsequent calls must do select-insert
               $listobject->querystring .= " INTO  $tstring $desttable ";
            }
            $listobject->querystring .= " FROM $regtab ";
            if ($debug) {
               print("$listobject->querystring ;<br>");
            }
            $listobject->performQuery();
            $k++;
         }
         $xlist = "'" . join("', '", $exes) . "'";
         $xcond = " $xcol not in ($xlist) ";
         #$debug = 0;
      } else {
         $xcond = ' (1 = 1) ';
      }

      if ($extrapall) {

         $listobject->querystring = "select $xcol ";
         $listobject->querystring .= "from $srctable ";
         $listobject->querystring .= " where $xcond ";
         $listobject->querystring .= " group by $xcol";
         $listobject->performQuery();
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }

         $trecs = $listobject->queryrecords;
         foreach($trecs as $rec) {
            $thisx = $rec[$xcol];
            $listobject->querystring = '';
            if ($k > 0) {
               # only use the prefix 'temp' for the initial creation,
               # subsequent calls must only insert
               $listobject->querystring .= " INSERT INTO $desttable ($keycols, $xcol, $extrapcol) ";
            }
            $listobject->querystring .= " SELECT $keycols, $thisx as $xcol,  ";
            $listobject->querystring .= " ( m * cast(($thisx - $xoff ) as float) + b ) AS $extrapcol ";
            if ($k == 0) {
               # only select-into for the initial creation,
               # subsequent calls must do select-insert
               $listobject->querystring .= " INTO  $tstring $desttable ";
            }
            $listobject->querystring .= " FROM $regtab ";
            if ($debug) {
               print("$listobject->querystring ;<br>");
            }
            $k++;
            $listobject->performQuery();
         }
      }
      /*
      # show regression info
      $listobject->querystring = " select *, ( m * cast(($thisx - $xoff ) as float) + b ) AS $extrapcol from $regtab  ";
      print("$listobject->querystring ;<br>");
      $listobject->performQuery();
      $listobject->showList();
      */

   }

   function genericMultiExtrap($listobject, $thisx, $srctable, $desttable, $xcol, $keycols, $extrapcols, $defaultvalue, $temptable, $usenull, $nullval, $rejectone, $debug, $extrapall=0, $zerox=0) {

      # this routine performs an extrapolation OR interpolation based on a least squares regression formula
      # this is based on the method used in the interpolation of ag census routines in SQL server, however,
      # it differs in that it handles both special cases where there is :
      #  1 data point - results in a constant value
      #  2 data points - results in a linear interpolation/extrapolation
      # it is up to the user to indicate that it wishes to reject 1 point cases by setting
      #   $rejectone = 1
      # $xcol = the name of the x-column of the destination table
      # $thisx = the values of the x-column to create estimates for

      # this routine WILL allow for negative values, so you must screen for these if this is not acceptable

      # $srctable - the table containing the values to draw from for this interpolation
      # $defaultmethod - how to handle cases where there is either 1 or zero bounding data points
      # This method expects there to be 2 points, one in the future, and one in the past in order to
      # perform an interpolation. If not, some method must be specified to arrive at a value for
      # this point.
      #       0 - Always use the $defaultvalue
      #       1 - Use only a prior point, otherwise, use the $defaultvalue
      #       2 - Use only a future point, otherwise, use the $defaultvalue
      #       3 - Use either a future point or past point rather than the $defaultvalue
      #            Check first for prior, then for future point

      # $temptable - whether or not to use the descriptor 'TEMP' in front of table creation syntax
      #              this serves two purposes
      #              1) it allows you to make either temporary or permanent tables with this
      #              2) it supports the temp table syntax used in MS SQL Server where the 'TEMP' keyword does not exist

      # $usenull - boolean indicates that there may be some other value that indicates a null
      # $nullval - if there is a value that indicates missing data other than NULL, check for it

      # $extrapall - whether or not to create an extrapolated value for every year in the data range, even for years in
      #   which there are already an existing value.

      # $zerox - whether or not to normalize x values on an intercept of 0.0 This helps often when the x-column in
      # a year, where we really want to do regression from 0-10 instead of 1992-2002.

      if ($zerox) {
         $listobject->querystring = "  SELECT min($xcol) as xoff ";
         $listobject->querystring .= " FROM $srctable ";
         if ($debug) {
            #print("Xoffset - $xoff <br>");
            print("$listobject->querystring ;<br>");
         }
         $listobject->performQuery();
         $xoff = $listobject->getRecordValue(1,'xoff');

      } else {
         $xoff = 0;
      }

      if ($temptable) {
         $tstring = 'TEMP';
      } else {
         $tstring = '';
      }

      # $keyvcols - comma separated list of columns that must match to interpolate data on
      $keyar = explode(",", $keycols);

      $regtab = ltrim(rtrim($desttable)) . "_regtable";
      $xxyy = ltrim(rtrim($desttable)) . "_xxyy";
      $sxxyy = ltrim(rtrim($desttable)) . "_sxxyy";
      $extrapcol = ltrim(rtrim($extrapcol));
      $xcol = ltrim(rtrim($xcol));
      $thisx = ltrim(rtrim($thisx));

      $extrapar = explode(',', $extrapcols);


      # create  base table of coefficients
      $listobject->querystring = " SELECT $keycols, count(*) AS numpoints, ";
      $listobject->querystring .= " avg(cast(($xcol - $xoff ) as float)) AS xm, ";
      $idel = '';
      $nullclause = " ( 1 = 1 ) ";
      foreach ($extrapar as $extrapcol) {
         $extrapcol = ltrim(rtrim($extrapcol));
         $listobject->querystring .= "$idel avg($extrapcol) AS ym_$extrapcol, ";
         $listobject->querystring .= " cast(0.000 AS float) as m_$extrapcol, ";
         $listobject->querystring .= " cast(0.000 AS float) as b_$extrapcol ";
         $idel = ',';
         $nullclause .= "    AND $extrapcol is not null ";
         if ($usenull) {
            # additional null criteria
            $nullclause .= "    AND $extrapcol <> $nullval ";
         }
      }
      $listobject->querystring .= " INTO $tstring $regtab ";
      $listobject->querystring .= " FROM $srctable ";
      $listobject->querystring .= " WHERE $nullclause ";
      $listobject->querystring .= " GROUP BY $keycols";
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }
      $listobject->performQuery();


      # reject values with only one data point?
      if ($rejectone) {
         $listobject->querystring = " DELETE FROM $regtab ";
         $listobject->querystring .= " WHERE numpoints = 1 ";
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }
         $listobject->performQuery();
      }


      # calculate basic  quantities, xx, yy, and xy
      $listobject->querystring = " SELECT (($xcol - $xoff ) - b.xm) AS xx, ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= " a.$thiskey ,";
      }
      $idel = '';
      $nullclause = " ( 1 = 1 ) ";
      foreach ($extrapar as $extrapcol) {
         $extrapcol = ltrim(rtrim($extrapcol));
         $listobject->querystring .= "$idel (a.$extrapcol - b.ym_$extrapcol) AS yy_$extrapcol ";
         $idel = ',';
         $nullclause .= "    AND a.$extrapcol is not null ";
         if ($usenull) {
            # additional null criteria
            $nullclause .= "    AND a.$extrapcol <> $nullval ";
         }
      }
      $listobject->querystring .= " INTO $tstring $xxyy ";
      $listobject->querystring .= " FROM $srctable AS a, $regtab AS b";
      $listobject->querystring .= " WHERE $nullclause ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND (a.$thiskey=b.$thiskey)  ";
      }
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }
      $listobject->performQuery();


      # calculate regression quantities, Sxx, Syy, Sxy
      $listobject->querystring = " SELECT $keycols, sum(xx*xx) AS Sxx, ";
      $idel = '';
      foreach ($extrapar as $extrapcol) {
         $extrapcol = ltrim(rtrim($extrapcol));
         $listobject->querystring .= "$idel sum(yy_$extrapcol * yy_$extrapcol) AS Syy_$extrapcol, ";
         $listobject->querystring .= " sum(xx * yy_$extrapcol) AS Sxy_$extrapcol ";
         $idel = ',';
      }
      $listobject->querystring .= " INTO $tstring $sxxyy ";
      $listobject->querystring .= " FROM $xxyy ";
      $listobject->querystring .= " GROUP BY $keycols ";
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }
      $listobject->performQuery();


      # calculate regression line slope and intercept for
      $listobject->querystring = " UPDATE $regtab SET ";
      $idel = '';
      foreach ($extrapar as $extrapcol) {
         $extrapcol = ltrim(rtrim($extrapcol));
         $listobject->querystring .= "$idel m_$extrapcol = ($sxxyy.Sxy_$extrapcol /$sxxyy.Sxx), ";
         $listobject->querystring .= "    b_$extrapcol = ";
         $listobject->querystring .= "       ( ym_$extrapcol - ( xm * $sxxyy.Sxy_$extrapcol / $sxxyy.Sxx ) ) ";
         $idel = ',';
      }
      $listobject->querystring .= " FROM $sxxyy ";
      $listobject->querystring .= " WHERE $sxxyy.Sxx <> 0 ";
      reset($keyar);
      foreach ($keyar as $thiskey) {
         $thiskey = ltrim(rtrim($thiskey));
         $listobject->querystring .= "    AND ($sxxyy.$thiskey = $regtab.$thiskey) ";
      }
      if ($debug) {
         print("$listobject->querystring ;<br>");
      }
      $listobject->performQuery();


      # if we choose to keep one point values, make them all eequal a constant
      #
      if (!$rejectone) {
         $listobject->querystring = " UPDATE $regtab SET ";
         $idel = '';
         foreach ($extrapar as $extrapcol) {
            $extrapcol = ltrim(rtrim($extrapcol));
            $listobject->querystring .= "$idel m_$extrapcol = 0, ";
            $listobject->querystring .= "    b_$extrapcol = ym_$extrapcol ";
            $idel = ',';
         }
         $listobject->querystring .= " FROM $sxxyy ";
         $listobject->querystring .= " WHERE numpoints = 1 ";
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }
         $listobject->performQuery();
      }

/*
      # show regression info
      $listobject->querystring = " select * from $regtab  ";
      $listobject->performQuery();
      $listobject->showList();
*/

      # create table of regression predicted values for the given input year
      # set up a counter to see if we have created the extrapolation table
      $k = 0;
      if ( strlen($thisx) > 0 ) {
         #$debug = 1;
         # we have called for one or more years to be specifically predicted
         $exes = explode(",", $thisx);
         foreach ($exes as $onex) {
            $listobject->querystring = '';
            if ($k > 0) {
               # only use the prefix 'temp' for the initial creation,
               # subsequent calls must only insert
               $listobject->querystring .= " INSERT INTO $desttable ($keycols, $xcol, $extrapcols) ";
            }
            $listobject->querystring .= " SELECT $keycols, $onex as $xcol,  ";
            $idel = '';
            foreach ($extrapar as $extrapcol) {
               $extrapcol = ltrim(rtrim($extrapcol));
               $listobject->querystring .= "$idel ( m_$extrapcol * cast(( $onex - $xoff ) as float) ";
               $listobject->querystring .= "    + b_$extrapcol ) AS $extrapcol ";
               $idel = ',';
            }
            if ($k == 0) {
               # only select-into for the initial creation,
               # subsequent calls must do select-insert
               $listobject->querystring .= " INTO  $tstring $desttable ";
            }
            $listobject->querystring .= " FROM $regtab ";
            if ($debug) {
               print("$listobject->querystring ;<br>");
            }
            $listobject->performQuery();
            $k++;
         }
         $xlist = "'" . join("', '", $exes) . "'";
         $xcond = " $xcol not in ($xlist) ";
         #$debug = 0;
      } else {
         $xcond = ' (1 = 1) ';
      }

      if ($extrapall) {

         $listobject->querystring = "select $xcol ";
         $listobject->querystring .= "from $srctable ";
         $listobject->querystring .= " where $xcond ";
         $listobject->querystring .= " group by $xcol";
         $listobject->performQuery();
         if ($debug) {
            print("$listobject->querystring ;<br>");
         }

         $trecs = $listobject->queryrecords;
         foreach($trecs as $rec) {
            $thisx = $rec[$xcol];
            $listobject->querystring = '';
            if ($k > 0) {
               # only use the prefix 'temp' for the initial creation,
               # subsequent calls must only insert
               $listobject->querystring .= " INSERT INTO $desttable ($keycols, $xcol, $extrapcol) ";
            }
            $listobject->querystring .= " SELECT $keycols, $thisx as $xcol,  ";
            $idel = '';
            foreach ($extrapar as $extrapcol) {
               $extrapcol = ltrim(rtrim($extrapcol));
               $listobject->querystring .= "$idel ( m_$extrapcol * cast(( $thisx - $xoff ) as float) ";
               $listobject->querystring .= "    + b_$extrapcol ) AS $extrapcol ";
               $idel = ',';
            }
            if ($k == 0) {
               # only select-into for the initial creation,
               # subsequent calls must do select-insert
               $listobject->querystring .= " INTO  $tstring $desttable ";
            }
            $listobject->querystring .= " FROM $regtab ";
            if ($debug) {
               print("$listobject->querystring ;<br>");
            }
            $k++;
            $listobject->performQuery();
         }
      }
      /*
      # show regression info
      $listobject->querystring = " select *, ( m * cast(($thisx - $xoff ) as float) + b ) AS $extrapcol from $regtab  ";
      print("$listobject->querystring ;<br>");
      $listobject->performQuery();
      $listobject->showList();
      */

   }


function doGenericCrossTab ($listobject, $basetable, $groupcols, $crosscol, $valcol, $ordercols = 1, $debug = 0, $cfunc='sum') {

   # returns a temp table aggregated via a cross-tab formula
   # from the basetable
   # takes a single column to create a cross value of its values


   $listobject->querystring = "select $crosscol from $basetable group by $crosscol";
   if ($ordercols) {
      $listobject->querystring .= " order by $crosscol";
   }
   if ($debug) { 
      error_log("$listobject->querystring<br>"); 
   }
   $listobject->performQuery();

   $crosscols = $listobject->queryrecords;

   $cdel = '';
   $conj = '';
   $conja = '';

   $cnames = '';
   $fromtabs = '';
   $condclause = '';
   $firstalias = '';
   $gcols = '';
   $jointext = '';
   
   $groupjoinsql = " (select $groupcols from $basetable group by $groupcols) as grp_$basetable ";
   $firstalias = "grp_$basetable";
   $thiscolfix = preg_replace("/[^a-zA-Z0-9_]/", '', $firstalias);
   $groupar = explode(",", str_replace(" ", '', $groupcols));
   foreach ($groupar as $thisgcol) {
      $gcols .= "$conja \"$thiscolfix\".$thisgcol ";
      $conja = ',';
   }
   # set the column firstalias to the first able alias name
   $firstalias = $thiscolfix;
   $jointext = ' left outer join ';
   $fromtabs .= $groupjoinsql;

   #print_r($crosscols);

   foreach ($crosscols as $thiscol) {

      $colname = $thiscol[$crosscol];

      //print("$colname <br>");
      $thiscolfix = preg_replace("/[^a-zA-Z0-9_]/", '', $colname);
      $cnames .= "$cdel \"$thiscolfix\".valcol as \"$thiscolfix\"";
      $fromtabs .= " $jointext ( select $groupcols, $cfunc($valcol) as valcol from $basetable where $crosscol = '$colname' group by $groupcols) as \"$thiscolfix\" ";
      reset($groupar);

      if ($firstalias <> '') {

         $condclause = " on ( ";
         $conj = '';
         foreach ($groupar as $thisgcol) {
           # $condclause .= "$conj \"$firstalias\".$thisgcol = \"$thiscolfix\".$thisgcol ";
            $condclause .= " $conj \"$firstalias\".$thisgcol = \"$thiscolfix\".$thisgcol ";
            $conj = 'and';
         }
         $condclause .= " ) ";
         $fromtabs .= $condclause;

      }

      # $firstalias = $thiscolfix;
      $cdel = ',';
   }

   # return "select $gcols, $cnames from $fromtabs where $condclause ";
   $query = "select $gcols, $cnames from $fromtabs ";
   if ($ordercols) {
      $query .= " order by $gcols ";
   }
   return $query;

}

?>